#！/usr/bin/python3
# -*- coding: utf-8 -*-
# @Time    : 2018/11/27 15:32
# @Author  : xuyanbin
# @Email   : xuyanbin-x@163.com
import oss2
import os
import pymysql

def get_file_url():
    '''
    获取OSS中所有图片的大小和url，并存储在当前路径的image.txt中
    :return:
    '''
    auth = oss2.Auth('LTAI27GnxNo87Vou','xjf8CeimFC53qJ0GLiFUbquHMQqda1')
    bucket = oss2.Bucket(auth,'http://oss-cn-shanghai.aliyuncs.com/NAMS','test50')
    #获取全部元信息
    # objectmeta = bucket.head_object("<yourObjectName>")
    # print(objectmeta.headers['Content-Type'])
    # 设置Delimiter参数为正斜线（/）。
    f = open('image.txt','a')
    for obj in oss2.ObjectIterator(bucket):
        try:
            objectmeta = bucket.head_object(obj.key)
            if objectmeta.headers['Content-Type'].startswith('image'):       #筛选图片格式的
                if int(objectmeta.headers['Content-Length'])/1024 <= 300:  #筛选小于300K的
                    print(obj.key)
                    print(int(objectmeta.headers['Content-Length'])/1024)
                    print(objectmeta.headers['Content-Type'])
                    print('-------------------------------')
                    f.write(obj.key +'|'+ str(int(objectmeta.headers['Content-Length'])/1024))
                    f.write('\n')
        except Exception as e:
            print(e)
            f.write(obj.key)
            f.write('\n')
            continue
    f.close()

def get_oss_size(obj_key):
    '''
    输入路径类似于NAMS/20181126124202266-zjpg.jpg，的格式。返回改文件的大小
    程序异常（传入的obj_key在oss上找不到，则返回200k）
    :param obj_key: 文件目录
    :return: 文件大小，单位是kb
    '''
    auth = oss2.Auth('LTAI27GnxNo87Vou', 'xjf8CeimFC53qJ0GLiFUbquHMQqda1')
    bucket = oss2.Bucket(auth, 'http://oss-cn-shanghai.aliyuncs.com/NAMS', 'test50')
    try:
        objectmeta = bucket.head_object(obj_key)
        size =  int(objectmeta.headers['Content-Length'])/1024
        return size
    except Exception as e :
        return 200



def get_bili(num):
    '''
    计算压缩比，根据图片大小和300K进行计算，取计算值100倍的整数部分。
    小于300K的图片不压缩返回100，压缩比小于1，OSS访问报错，太大的文件压缩比设置为1
    :param num:  传入的图片大小
    :return: 返回压缩比，为int类型
    '''
    if num < 300:
        return 100
    else:
        duibi = 300
        # bili = '%.2f'%300/num   #保留两位小数
        bili = (300/num)*100
        if bili < 1:
            bili = 1
        return int(bili)
        # return bili



def db_com():
    '''
    将数据库查询的url进行字符串处理，处理成oss中obj.key的格式。然后到oss上获取大小，小于300K的文件显示原图
    大于300K的文件进行10倍压缩。
    程序运行完成得到sql文件，保存在当前路径为update_sql.txt
    :return:
    '''
    # conn = pymysql.connect(host="10.0.20.40", user="gpai_mysql", passwd="gpai1@#", db="nams", port=3306,charset="utf8")
    conn = pymysql.connect(host="10.0.20.105", user="pft_mysql", passwd="pft@gpai", db="nams", port=3306,charset="utf8")
    cur = conn.cursor()
    sql = 'select id,pic_url from product'
    cur.execute(sql)
    results = cur.fetchall()
    for i in results:
        id = i[0]
        url = i[1]
        temp = []
        # print(url)
        if url :  #去除没有url的
            # print(id)
            # print(url)
            # print(url.split(';'))
            for b in url.split(';'):
                b1 = b.replace('http://cdnimg.gpai.net/','')
                b2 = b1.replace('http://test50.oss-cn-shanghai.aliyuncs.com/','')
                b3 = b2.split('?')[0]
                b3_size  = get_oss_size(b3)  #到oss上判断文件大小，得到KB
                num = get_bili(b3_size)      #计算压缩比
                b4 = 'http://cdnimg.gpai.net/%s?x-oss-process=image/resize,p_%s'%(b3,num)
                temp.append(b4)

            new_url = ";".join(temp)
            sql ="update product set pic_url='%s' where id='%s';"%(new_url,id)
            # print(new_url)
            print(sql)
            with open('update_sql.txt','a') as f :
                f.write(sql)
                f.write('\n')
db_com()